﻿<%@ WebHandler Language="C#" Class="MESDataReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

public class MESDataReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    UserImage u = new UserImage();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        //查询
        if (jObject["type"] != null && jObject["type"].ToString() == "getData")
        {
            DataTable dt = getData(jObject);
            Result r = new Result();
            r.data = dt;
            r.total = Convert.ToInt32(dt.Rows.Count);
            r.meta = new Result.Meta(200, "请求成功");
            context.Response.ContentType = "text/json";
            context.Response.Write(JsonConvert.SerializeObject(r));
            context.Response.End();
        }

        //导出
        if (jObject["type"] != null && jObject["type"].ToString() == "exportExcel")
        {
            DataTable dt = getData(jObject);

            dt.Columns["factoryname"].Caption = "分厂";
            dt.Columns["prepqty"].Caption = "生产准备";
            dt.Columns["shipqty"].Caption = "转工协作";
            dt.Columns["issueqty"].Caption = "集件功能";
            dt.Columns["nonqty"].Caption = "不合格审理";
            dt.Columns["repqty"].Caption = "报工";
            dt.Columns["stepplanedqty"].Caption = "报工中已排产";
            dt.Columns["repinhourqty"].Caption = "1小时内报工";
            dt.Columns["wsqty"].Caption = "无损接收";
            dt.Columns["wsoneminqty"].Caption = "无损接收（1分钟内完成）";
            ExportHelper.ExportExcel(dt, "MES数据查询报表");
        }
    }

    //查询方法
    DataTable getData(JObject jObject)
    {
        JArray arr = JArray.Parse(jObject["selectdate"].ToString());

        //查询机匣、结构件、燃烧、盘轴、装试
        string sql = @" SELECT f.factoryname
                            FROM factory f
                            LEFT JOIN Userattribute ua ON f.factoryid = ua.parentid AND ua.userattributename = '分厂代码'
                            WHERE ua.attributevalue IN ('601','609','607','600','603','608','604','602')";
        DataTable dtFactory = dal.GetDataTable(sql);
        dtFactory.Columns.Add("prepQty");//生产准备
        dtFactory.Columns.Add("shipQty");//转工协作
        dtFactory.Columns.Add("issueQty");//集件功能
        dtFactory.Columns.Add("nonQty");//不合格审理
        dtFactory.Columns.Add("repQty");//报工
        dtFactory.Columns.Add("repInHourQty");//一小时内报工
        dtFactory.Columns.Add("stepPlanedQty");//报工中已排产
        dtFactory.Columns.Add("wsQty");//无损接收任务
        dtFactory.Columns.Add("wsOneMinQty");//无损接收任务（1分钟内完成）

        //生产准备
        sql = @"
SELECT f.factoryname,COUNT(ar.containerid) qty
FROM arrangements ar
LEFT JOIN containercurrentinfo co ON co.containerid = ar.containerid
LEFT JOIN factory f ON f.factoryid = co.originalfactoryid
WHERE TRUNC(ar.createdate) BETWEEN TO_DATE('{0}','YYYY-MM-DD') AND TO_DATE('{1}','yyyy-mm-dd')
GROUP BY f.factoryname
";
        sql = string.Format(sql, arr[0].ToString(), arr[1].ToString());
        DataTable dtPrepData = dal.GetDataTable(sql);

        //转工协作
        sql = @"
SELECT f.factoryname,COUNT(csrd.containershiprecordid) qty
FROM containershiprecord csrd
LEFT JOIN factory f ON f.factoryid = csrd.factoryid
WHERE csrd.isuse = 0 AND TRUNC(csrd.senddate) BETWEEN TO_DATE('{0}','YYYY-MM-DD') AND TO_DATE('{1}','YYYY-MM-DD')
GROUP BY f.factoryname
";
        sql = string.Format(sql, arr[0].ToString(), arr[1].ToString());
        DataTable dtShipData = dal.GetDataTable(sql);

        //集件功能
        sql = @"
SELECT f.factoryname,COUNT(il.issueerpmaterialid) qty
FROM issueerpmaterial il
LEFT JOIN factory f ON f.factoryid = il.factoryid
WHERE TRUNC(il.createdate) BETWEEN TO_DATE('{0}','YYYY-MM-DD') AND TO_DATE('{1}','YYYY-MM-DD')
GROUP BY f.factoryname
";
        sql = string.Format(sql, arr[0].ToString(), arr[1].ToString());
        DataTable dtIssueData = dal.GetDataTable(sql);

        //不合格审理
        sql = @"
SELECT f.factoryname,COUNT(cnn.containernonconforminginfoid) qty
FROM containernonconforminginfo cnn
LEFT JOIN containercurrentinfo co ON co.containerid = cnn.containerid
LEFT JOIN factory f ON f.factoryid = co.originalfactoryid
WHERE TRUNC(cnn.createdate) BETWEEN TO_DATE('{0}','YYYY-MM-DD') AND TO_DATE('{1}','YYYY-MM-DD')
GROUP BY f.factoryname
";
        sql = string.Format(sql, arr[0].ToString(), arr[1].ToString());
        DataTable dtNonData = dal.GetDataTable(sql);

        //报工
        sql = @"
SELECT f.factoryname,COUNT(csw.workflowstepid) qty
FROM containerstepworkreport csw
LEFT JOIN containerstepdispatch csd ON csd.containerstepdispatchid = csw.containerstepdispatchid
LEFT JOIN factory f ON f.factoryid = csw.factoryid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csw.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec sp ON sp.specid = NVL(sb.revofrcdid,ws.specid)
WHERE csw.isuse = 0 AND csd.isuse = 0 
      AND ws.workflowstepname NOT LIKE '%洗%' AND ws.workflowstepname NOT LIKE '%油%' AND ws.workflowstepname NOT LIKE '%库%' 
      AND sp.isspotcheck <> 1 
      AND TRUNC(csw.createdate) BETWEEN TO_DATE('{0}','YYYY-MM-DD') AND TO_DATE('{1}','YYYY-MM-DD')
GROUP BY f.factoryname
";
        sql = string.Format(sql, arr[0].ToString(), arr[1].ToString());
        DataTable dtRepData = dal.GetDataTable(sql);

        //一小时内报工
        sql = @"
SELECT f.factoryname,COUNT(csw.workflowstepid) qty
FROM containerstepworkreport csw
LEFT JOIN containerstepdispatch csd ON csd.containerstepdispatchid = csw.containerstepdispatchid
LEFT JOIN factory f ON f.factoryid = csw.factoryid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csw.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec sp ON sp.specid = NVL(sb.revofrcdid,ws.specid)
WHERE csw.isuse = 0 AND csd.isuse = 0 
      AND ws.workflowstepname NOT LIKE '%洗%' AND ws.workflowstepname NOT LIKE '%油%' AND ws.workflowstepname NOT LIKE '%库%'  
      AND sp.isspotcheck <> 1 
      AND (csw.createdate - csd.createdate) * 24 <= 1 
      AND TRUNC(csw.createdate) BETWEEN TO_DATE('{0}','YYYY-MM-DD') AND TO_DATE('{1}','YYYY-MM-DD')
GROUP BY f.factoryname
";
        sql = string.Format(sql, arr[0].ToString(), arr[1].ToString());
        DataTable dtRepInHourData = dal.GetDataTable(sql);

        //报工中已排产
        sql = @"
SELECT f.factoryname,COUNT(csw.workflowstepid) qty
FROM containerstepworkreport csw
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csw.workflowstepid
INNER JOIN containerstepplan cp ON cp.containerid = csw.containerid AND cp.workflowstepid = csw.workflowstepid AND cp.plantype = 1
LEFT JOIN factory f ON f.factoryid = csw.factoryid
LEFT JOIN containerstepdispatch csd ON csd.containerstepdispatchid = csw.containerstepdispatchid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec sp ON sp.specid = NVL(sb.revofrcdid,ws.specid)
WHERE csw.isuse = 0 AND csd.isuse = 0
      AND sp.isspotcheck <> 1 
      AND ws.workflowstepname NOT LIKE '%洗%' AND ws.workflowstepname NOT LIKE '%油%' AND ws.workflowstepname NOT LIKE '%库%' 
      AND TRUNC(csw.createdate) BETWEEN TO_DATE('{0}','YYYY-MM-DD') AND TO_DATE('{1}','YYYY-MM-DD')
GROUP BY f.factoryname
";
        sql = string.Format(sql, arr[0].ToString(), arr[1].ToString());
        DataTable dtStepPlanedData = dal.GetDataTable(sql);

        //无损接收任务
        sql = @"
SELECT f.factoryname,COUNT(DISTINCT csrv.containershiprecordid) qty
FROM containershiprecord csrd
LEFT JOIN containershiprecive csrv ON csrd.containershiprecordid = csrv.containershiprecordid
LEFT JOIN factory f1 ON f1.factoryid = csrv.factoryid
LEFT JOIN factory f ON f.factoryid = csrv.tofactoryid
LEFT JOIN containershiprecordstep csds ON csds.containershiprecordid = csrv.containershiprecordid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csds.workflowstepid
WHERE csrd.isuse = 0 AND f1.factoryname = '无损检测中心' AND ws.workflowstepname NOT LIKE '%磁%' AND ws.workflowstepname NOT LIKE '%超%'
      AND TRUNC(csrd.toacceptdate) BETWEEN TO_DATE('{0}','YYYY-MM-DD') AND TO_DATE('{1}','YYYY-MM-DD')
GROUP BY f.factoryname
";
        sql = string.Format(sql, arr[0].ToString(), arr[1].ToString());
        DataTable dtWSData = dal.GetDataTable(sql);

        //无损接收任务（1分钟内完成）
        sql = @"
SELECT f.factoryname,COUNT(DISTINCT csrv.containershiprecordid) qty
FROM containershiprecord csrd
LEFT JOIN containershiprecive csrv ON csrd.containershiprecordid = csrv.containershiprecordid
LEFT JOIN factory f1 ON f1.factoryid = csrv.factoryid
LEFT JOIN factory f ON f.factoryid = csrv.tofactoryid
LEFT JOIN containershiprecordstep csds ON csds.containershiprecordid = csrv.containershiprecordid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csds.workflowstepid
WHERE csrd.isuse = 0 AND f1.factoryname = '无损检测中心' AND ws.workflowstepname NOT LIKE '%磁%' AND ws.workflowstepname NOT LIKE '%超%'
      AND (csrv.completedate - csrd.toacceptdate) *24*60 <=1 
      AND TRUNC(csrd.toacceptdate) BETWEEN TO_DATE('{0}','YYYY-MM-DD') AND TO_DATE('{1}','YYYY-MM-DD')
GROUP BY f.factoryname
";
        sql = string.Format(sql, arr[0].ToString(), arr[1].ToString());
        DataTable dtWSOneMinData = dal.GetDataTable(sql);

        foreach (DataRow dr in dtFactory.Rows)
        {
            string filter = string.Format(" factoryname = '{0}' ", dr["factoryname"].ToString());
            dr["prepQty"] = dtPrepData.Select(filter).Length > 0 ? dtPrepData.Select(filter)[0]["qty"] : 0;
            dr["shipQty"] = dtShipData.Select(filter).Length > 0 ? dtShipData.Select(filter)[0]["qty"] : 0;
            dr["issueQty"] = dtIssueData.Select(filter).Length > 0 ? dtIssueData.Select(filter)[0]["qty"] : 0;
            dr["nonQty"] = dtNonData.Select(filter).Length > 0 ? dtNonData.Select(filter)[0]["qty"] : 0;
            dr["repQty"] = dtRepData.Select(filter).Length > 0 ? dtRepData.Select(filter)[0]["qty"] : 0;
            dr["repInHourQty"] = dtRepInHourData.Select(filter).Length > 0 ? dtRepInHourData.Select(filter)[0]["qty"] : 0;
            dr["stepPlanedQty"] = dtStepPlanedData.Select(filter).Length > 0 ? dtStepPlanedData.Select(filter)[0]["qty"] : 0;
            dr["wsQty"] = dtWSData.Select(filter).Length > 0 ? dtWSData.Select(filter)[0]["qty"] : 0;
            dr["wsOneMinQty"] = dtWSOneMinData.Select(filter).Length > 0 ? dtWSOneMinData.Select(filter)[0]["qty"] : 0;
        }

        foreach (DataColumn col in dtFactory.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        return dtFactory;
    }
}